package tech.yangzhijie.basic.jdbc;



import tech.yangzhijie.basic.jdbc.bean.User;
import tech.yangzhijie.basic.jdbc.util.JDBCUtil;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class JdbcTest {

    public static void main(String[] args) {
        query();
        insert();
        delete();
        update();
        query();
    }

    public static void insert(){
        String insert = "INSERT user(username,age) VALUES(?,?)";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //执行sql语句
        try {
            preparedStatement = connection.prepareStatement(insert);
            preparedStatement.setString(1, "杨幂");
            preparedStatement.setInt(2, 16);
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("添加成功！");
            }else {
                System.out.println("添加失败！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(preparedStatement != null){
                JDBCUtil.close(preparedStatement);
            }
            if(connection != null){
                JDBCUtil.close(connection);
            }
        }
    }

    public static void delete(){
        String delete = "DELETE FROM user WHERE id = 2";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //执行sql语句
        try {
            preparedStatement = connection.prepareStatement(delete);
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }else {
                System.out.println("删除失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(preparedStatement != null){
                JDBCUtil.close(preparedStatement);
            }
            if(connection != null){
                JDBCUtil.close(connection);
            }
        }
    }

    public static void update(){
        String update = "UPDATE user SET username = ?,age = ?,update_time = NOW() WHERE id = ?";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        //执行sql语句
        try {
            preparedStatement = connection.prepareStatement(update);
            preparedStatement.setString(1, "杨幂");
            preparedStatement.setInt(2, 18);
            preparedStatement.setInt(3, 6);
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("修改成功！");
            }else {
                System.out.println("修改失败！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(preparedStatement != null){
                JDBCUtil.close(preparedStatement);
            }
            if(connection != null){
                JDBCUtil.close(connection);
            }
        }
    }


    private static DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    public static void query(){
        String query = "SELECT id,username,age,create_time,update_time from user";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> queryList = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(query);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                int age = resultSet.getInt("age");
                Timestamp create_time = resultSet.getTimestamp("create_time");
                Timestamp update_time = resultSet.getTimestamp("update_time");

                LocalDateTime createTime = create_time.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                LocalDateTime updateTime = update_time.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                queryList.add(new User(id, username, age,createTime,updateTime));
            }
            queryList.forEach(e->
                System.out.printf("%d\t\t%s\t\t%d\t\t%s\t\t%s\n",e.getId(),e.getUsername(),e.getAge(),
                        dateTimeFormatter.format(e.getCreateTime()),
                        dateTimeFormatter.format(e.getUpdateTime()))
            );
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null){
                JDBCUtil.close(connection);
            }
            if(preparedStatement != null){
                JDBCUtil.close(preparedStatement);
            }
            if(resultSet != null){
                JDBCUtil.close(resultSet);
            }
        }
    }
}
